﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using DBUtil;
namespace ZXCompany.Models.Dao
{
    public class ZX_ExpertTypeDao
    {
        private Database db = new Database();
        private List<Entity.ZX_ExpertType> GetListFromDT(DataTable dt)
        {
            List<Entity.ZX_ExpertType> lexpert = new List<Entity.ZX_ExpertType>();
            foreach (DataRow dr in dt.Rows)
            {
                Entity.ZX_ExpertType expert = new Entity.ZX_ExpertType();
                expert.ID = Convert.ToInt32(dr["ID"]);
                expert.sort = Convert.ToInt32(dr["sort"]);
                expert.updateTime = dr["updateTime"].ToString();
                expert.etype = dr["etype"].ToString();
                expert.addUserID = Convert.ToInt32(dr["addUserID"]);
                expert.addTime = dr["addTime"].ToString();
                lexpert.Add(expert);
            }
            return lexpert;
        }
        /// <summary>
        /// 获取专家类别
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<Entity.ZX_ExpertType> Select(SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                                      ,isnull([etype],'') as [etype]
                                      ,isnull([sort],0) as [sort]
                                      ,isnull([addUserID],0) as [addUserID]
                                      ,isnull([addTime],'') as [addTime]
                                      ,isnull([updateTime],'') as [updateTime]
                                  FROM [ZX_ExpertType]";
                DataTable dt = db.Squery(sql, sconn);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 通过ID获取专家类别
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<Entity.ZX_ExpertType> SelectById(int etid,SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                            ,isnull([etype],'') as [etype]
                            ,isnull([sort],0) as [sort]
                            ,isnull([addUserID],0) as [addUserID]
                            ,isnull([addTime],'') as [addTime]
                            ,isnull([updateTime],'') as [updateTime]
                        FROM [ZX_ExpertType] where id=@id";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@id",etid)
                };
                DataTable dt = db.Squery(sql, sconn, para);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 通过ID获取专家类别
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public int SelectMaxId(SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull(max(sort),0) + 1 as sort FROM [ZX_ExpertType]";
                DataTable dt = db.Squery(sql, sconn);
                return Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 通过ID获取专家类别
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public int SelectMaxId(string tabstr,string fieldstr,SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull(max(" + fieldstr + "),0) + 1 as sort FROM " + tabstr;
                DataTable dt = db.Squery(sql, sconn);
                return Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 通过ID删除专家类别
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Delete(int etid, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"delete FROM [ZX_ExpertType] where id=@id";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@id",etid)
                };
                DataTable dt = db.Squery(sql, sconn, stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过ID更新专家类别
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Update(Entity.ZX_ExpertType Entype, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"Update ZX_ExpertType set
                            etype =@etype
                            ,sort=@sort
                            ,addUserID=@addUserID
                            ,updateTime=@updateTime
                            where id=@id";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@id",Entype.ID),
                    new SqlParameter("@etype",Entype.etype),
                    new SqlParameter("@sort",Entype.sort),
                    new SqlParameter("@addUserID",Entype.addUserID),
                    new SqlParameter("@updateTime",Entype.updateTime)
                };
                DataTable dt = db.Squery(sql, sconn,stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过ID更新专家类别
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Insert(Entity.ZX_ExpertType Entype, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"Insert into ZX_ExpertType (etype,sort,addUserID,addTime) values
                             (@etype,@sort,@addUserID,@addTime)";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@etype",Entype.etype),
                    new SqlParameter("@sort",Entype.sort),
                    new SqlParameter("@addUserID",Entype.addUserID),
                    new SqlParameter("@addTime",Entype.addTime)
                };
                db.Udaquery(sql, sconn, stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}